package com.medical.standard.dao;

import com.medicalstandard.entity.Standard;
import com.medicalstandard.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StandardDAO {

    public boolean addStandard(Standard standard) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO standard (standard_code, standard_name, english_name, reference_type, publish_date, implementation_date) VALUES (?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, standard.getStandardCode());
            pstmt.setString(2, standard.getStandardName());
            pstmt.setString(3, standard.getEnglishName());
            pstmt.setString(4, standard.getReferenceType());
            pstmt.setDate(5, new Date(standard.getPublishDate().getTime()));
            pstmt.setDate(6, new Date(standard.getImplementationDate().getTime()));

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }

    public boolean updateStandard(Standard standard) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE standard SET standard_code=?, standard_name=?, english_name=?, reference_type=?, publish_date=?, implementation_date=? WHERE id=?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, standard.getStandardCode());
            pstmt.setString(2, standard.getStandardName());
            pstmt.setString(3, standard.getEnglishName());
            pstmt.setString(4, standard.getReferenceType());
            pstmt.setDate(5, new Date(standard.getPublishDate().getTime()));
            pstmt.setDate(6, new Date(standard.getImplementationDate().getTime()));
            pstmt.setInt(7, standard.getId());

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }

    public boolean deleteStandard(int standardId) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM standard WHERE id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, standardId);

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }

    public Standard getStandardById(int standardId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Standard standard = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM standard WHERE id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, standardId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                standard = new Standard();
                standard.setId(rs.getInt("id"));
                standard.setStandardCode(rs.getString("standard_code"));
                standard.setStandardName(rs.getString("standard_name"));
                standard.setEnglishName(rs.getString("english_name"));
                standard.setReferenceType(rs.getString("reference_type"));
                standard.setPublishDate(rs.getDate("publish_date"));
                standard.setImplementationDate(rs.getDate("implementation_date"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return standard;
    }

    public List<Standard> getAllStandards() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Standard> standards = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM standard ORDER BY id";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Standard standard = new Standard();
                standard.setId(rs.getInt("id"));
                standard.setStandardCode(rs.getString("standard_code"));
                standard.setStandardName(rs.getString("standard_name"));
                standard.setEnglishName(rs.getString("english_name"));
                standard.setReferenceType(rs.getString("reference_type"));
                standard.setPublishDate(rs.getDate("publish_date"));
                standard.setImplementationDate(rs.getDate("implementation_date"));
                standards.add(standard);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return standards;
    }

    // 根据标准编号查询
    public Standard getStandardByCode(String standardCode) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Standard standard = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM standard WHERE standard_code=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, standardCode);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                standard = new Standard();
                standard.setId(rs.getInt("id"));
                standard.setStandardCode(rs.getString("standard_code"));
                standard.setStandardName(rs.getString("standard_name"));
                standard.setEnglishName(rs.getString("english_name"));
                standard.setReferenceType(rs.getString("reference_type"));
                standard.setPublishDate(rs.getDate("publish_date"));
                standard.setImplementationDate(rs.getDate("implementation_date"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return standard;
    }
}